package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import Helper.Util;
import Model.Base;

public class BaseDAO implements ItfBanco<Base>{
	
	@Override
	public void inserir(Base base) throws SQLException {
		Connection con = Conexao.getConexao();
		String sql = "INSERT INTO BASE VALUES(?, ?)";
		
		//DECLARE @id INT SET @id = SCOPE_IDENTITY()
		try(PreparedStatement pstm = con.prepareStatement(sql)) {
			
			pstm.setString(1, base.getNome());
			pstm.setInt(2, base.getStatus());
			
			pstm.execute();
			//con.commit();
			
		} catch (Exception e) {
			//con.rollback();
			System.out.println("Erro ao inserir base: ");
			e.printStackTrace();
		}
		
		con.close();
	}

	@Override
	public void atualizar(Base base) throws SQLException {
		Connection con = Conexao.getConexao();
		String sql = "UPDATE BASE SET"
				+ "			NOME_BASE = ?,"
				+ "         STATUS_BASE = ?"
				+ "   WHERE ID_BASE = ?";
		
		try(PreparedStatement pstm = con.prepareStatement(sql)) {
			
			pstm.setString(1, base.getNome());
			pstm.setInt(2, base.getStatus());
			pstm.setInt(3, base.getId());
			
			pstm.executeUpdate();
			con.commit();
			
		} catch (Exception e) {
			con.rollback();
			System.out.println("Erro ao atualizar base: ");
			e.printStackTrace();
		}
		
		con.close();
	}

	@Override
	public Base listar(Base base) throws SQLException {
		Connection con = Conexao.getConexao();
		
		String sql = "SELECT ID_BASE,"
				+ "          NOME_BASE, "
				+ "          STATUS_BASE"
				+ "   FROM BASE"
				+ "   WHERE ID_BASE = ?";
		
		ResultSet rs;
		
		//DECLARE @id INT SET @id = SCOPE_IDENTITY()
		try(PreparedStatement pstm = con.prepareStatement(sql)) {
			
			pstm.setInt(1, base.getId());
			
			rs = pstm.executeQuery();
			
			while(rs.next()){
				base.setId(rs.getInt("ID_BASE"));
				base.setNome(rs.getString("NOME_BASE"));
				base.setStatus(rs.getInt("STATUS_BASE"));
			}
			
		} catch (Exception e) {
			System.out.println("Erro ao listar Base: ");
			e.printStackTrace();
		}	
		
		con.close();
		return base;
	}

	@Override
	public ArrayList<Base> listarTodos() throws SQLException {
		
		ArrayList<Base> lista = new ArrayList<>();
		
		Connection con = Conexao.getConexao();
		String sql = "SELECT ID_BASE,"
				+ "          NOME_BASE,"
				+ "          STATUS_BASE "
				+ "   FROM BASE "
				+ "	  WHERE STATUS_BASE <> ?";
		
		ResultSet rs;
		PreparedStatement pstm = con.prepareStatement(sql);
		pstm.setInt(1, Util.stExcluido);
		
		rs = pstm.executeQuery();
		
		while (rs.next()){
			Base base = new Base();
			base.setId(rs.getInt("ID_BASE"));
			base.setNome(rs.getString("NOME_BASE"));
			base.setStatus(rs.getInt("STATUS_BASE"));
			lista.add(base);
		}
		
		con.close();
		return lista;
	}
 
}
